﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Windows.Forms;

namespace sands
{
    class DataBase
    {
        /* "Database structure"
      * Index
      * Imie
      * Nazwisko
      * Ulica
      * KodIMiasto
      * mobile number
      * home number
      * mums phone
      * dads phone
      */

        private System.Data.DataSet dataset;
        uint baseValue = 0;
        private static DataBase instance = null;

        public DataSet Dataset
        {
            get
            {
                return dataset;
            }
        }
        public bool isEmpty
        {
            get
            {
                return dataset.Tables["uczniowie"].Rows.Count == 0 ? true : false;
            }
        }
        public uint BaseValue
        {
            get
            {
                return baseValue;
            }
        }

        //studentColumns in the database
        private String[] studentColumns = new String[] { "Index", "Imie", "Nazwisko", "Ulica", "KodIMiasto","Mobile","Domowy","MamaNum","TataNum","DataRozpoczecia","Inne" };
        private String[] depositColumns = new String[] { "Index", "Data", "Numer", "Tytul", "Kwota" };

        public String ReportNumber
        {
            get
            {
                if (!String.IsNullOrEmpty(dataset.Tables["ustawienia"].Rows[0].ItemArray[dataset.Tables["ustawienia"].Columns.IndexOf("numer")].ToString()))
                {
                    return ((String)dataset.Tables["ustawienia"].Rows[0].ItemArray[dataset.Tables["ustawienia"].Columns.IndexOf("numer")]);
                }
                else
                    return String.Empty;
            }
            set
            {
                String[] tmp = new String[2];
                dataset.Tables["ustawienia"].Rows[0].ItemArray.CopyTo(tmp,0);
                dataset.Tables["ustawienia"].Rows.RemoveAt(0);
                tmp[dataset.Tables["ustawienia"].Columns.IndexOf("numer")] = value;
                dataset.Tables["ustawienia"].Rows.Add(tmp);
                Dataset.AcceptChanges();
                Dataset.WriteXml("baza.xml");
            }
        }

        /// <summary>
        /// Default constructor. Uses Columns array to populate dataset's column info.
        /// </summary>
        private DataBase()
        {
            try
            {
                dataset = new System.Data.DataSet("SandS");
                
                LoadDatabase();
                
                if(dataset.Tables["ustawienia"].Rows.Count>0)
                    uint.TryParse(dataset.Tables["ustawienia"].Rows[0].ItemArray[0].ToString(), out baseValue);

                DataRelation studentDepositRelation = dataset.Relations.Add("studentDepositRelation", dataset.Tables["uczniowie"].Columns["Index"], dataset.Tables["wplaty"].Columns["Index"]);
                dataset.Tables["uczniowie"].PrimaryKey = new DataColumn[] { dataset.Tables["uczniowie"].Columns[0] }; //set primary key
                dataset.AcceptChanges();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message, "Błąd: inicjalizacja bazy danych", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            
        }

        public static DataBase GetInstance()
        {
            if (instance == null)
                instance = new DataBase();

            return instance;
        }

        /// <summary>
        /// If exists, loads database from the "baza.xml" file. Also makes sure the database works properly with the data availible (NOTE::incomplete file, no file)
        /// </summary>
        /// <exception cref="FormatException">Throws when columnsNames don't match</exception>
        public void LoadDatabase()
        {
            try
            {
              /*  if (System.IO.File.Exists("baza.ratuj")) //if ratuj exists?
                {
                    if (DialogResult.Yes == MessageBox.Show("Znaleziono kopię zapasową bazy danych. Czy chcesz odtworzyć bazę z tego pliku?", "Kopia zapasowa", MessageBoxButtons.YesNo, MessageBoxIcon.Question))
                    {
                        System.IO.FileStream file = new System.IO.FileStream("baza.ratuj", System.IO.FileMode.Open);
                        System.IO.Compression.GZipStream backup = new System.IO.Compression.GZipStream(file, System.IO.Compression.CompressionMode.Decompress);
                        dataset.ReadXml(backup);
                    }
                }*/

                if (System.IO.File.Exists("baza.xml")) //if db exists?
                {
                    dataset.ReadXml("baza.xml");
                }

                if (dataset.Tables.Contains("uczniowie"))
                {
                    if (dataset.Tables["uczniowie"].Columns.Count != studentColumns.Length)
                        throw new FormatException("Baza danych w złym formacie.\n(Niezgodna ilość kolumn w tablicy \"uczniowie\")");
                }
                else
                {
                    dataset.Tables.Add("uczniowie");    //create new students table
                    for (int i = 0; i < studentColumns.Length; i++)
                        dataset.Tables["uczniowie"].Columns.Add(studentColumns[i]);
                }

                if (dataset.Tables.Contains("wplaty"))
                {
                    if (dataset.Tables["wplaty"].Columns.Count != depositColumns.Length)
                        throw new FormatException("Baza danych w złym formacie.\n(Niezgodna ilość kolumn w tablicy \"wplaty\")");
                }
                else
                {
                    dataset.Tables.Add("wplaty");
                    for (int i = 0; i < depositColumns.Length; i++)
                        dataset.Tables["wplaty"].Columns.Add(depositColumns[i]);
                }

                if (!dataset.Tables.Contains("ustawienia"))
                {
                    dataset.Tables.Add("ustawienia");
                    dataset.Tables["ustawienia"].Columns.Add("kwota");
                    dataset.Tables["ustawienia"].Columns.Add("numer");
                    dataset.Tables["ustawienia"].Rows.Add(new String[] {String.Empty,String.Empty} );

                    TrySetBaseValue();
                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        /// <summary>
        /// Adds Student object to the database
        /// </summary>
        /// <param name="student">Student to be added</param>
        public void AddStudent(Student student)
        {
            try
            {
                dataset.Tables["uczniowie"].Rows.Add(student.ToStringArray());
                TrySave();
            }
            catch (ConstraintException cons)
            {
                throw cons;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message, "Błąd: dodawanie ucznia", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }

        public void AddDeposit(Student student, Deposit deposit)
        {
            // { "Index", "Data", "Numer", "Tytul", "Kwota" };

            String[] tmp = new String[depositColumns.Length];
            tmp[0] = student.Index;
            Array.Copy(deposit.ToStringArray(), 0, tmp, 1, deposit.ToStringArray().Length);
            dataset.Tables["wplaty"].Rows.Add(tmp);

            TrySave();
        }

        public void ChangeDepositID(String oldID, String newID)
        {
            foreach(DataRow dr in dataset.Tables["wplaty"].Rows)
                dr.ItemArray[0] = newID;
        }

        public void RemoveDeposits(String index)
        {
            int i = 0;
            while (i < dataset.Tables["wplaty"].Rows.Count)
            {
                DataRow dr = dataset.Tables["wplaty"].Rows[i];
                if (dr.ItemArray[0].Equals(index))
                {
                    dataset.Tables["wplaty"].Rows.Remove(dr);
                }
                else
                    ++i;
            }
        }

        public DataRow[] GetDeposits(Student student)
        {
            return dataset.Tables["uczniowie"].Rows.Find(student.Index).GetChildRows("studentDepositRelation");
        }

        public void SetBaseValue(uint baseValue)
        {
            String[] tmp = new String[2];
            dataset.Tables["ustawienia"].Rows[0].ItemArray.CopyTo(tmp, 0); //copies old value
            dataset.Tables["ustawienia"].Rows.RemoveAt(0); //removes row from the database
            tmp[dataset.Tables["ustawienia"].Columns.IndexOf("kwota")] = baseValue.ToString(); //adds new base value
            dataset.Tables["ustawienia"].Rows.Add(tmp); //adds new row

            TrySave();
        }

        public void Update()
        {
            foreach(DataRow row in dataset.Tables["uczniowie"].Rows)
            {
                Object[] array = row.ItemArray;
                DateTime date = DateTime.Parse(array[9].ToString());

                if (date.Year < DateTime.Now.Year)
                    date = date.AddYears(1);

                array[9] = date.ToShortDateString();
                dataset.Tables["uczniowie"].LoadDataRow(array, true);

            }
            MessageBox.Show("Aktualizacja zakończona");
            TrySave();
        }

        /// <summary>
        /// Edits student's record in the database
        /// </summary>
        /// <param name="student">Student to be edited</param>
        public void Edit(Student oldStudent,Student newStudent)
        {
            try
            {
                DataRow studentRow = dataset.Tables["uczniowie"].Rows[dataset.Tables["uczniowie"].Rows.IndexOf(dataset.Tables["uczniowie"].Rows.Find(oldStudent.Index))];
                studentRow.ItemArray = newStudent.ToStringArray();
                TrySave();
            }
            catch (ConstraintException cons)
            {
                MessageBox.Show(cons.Message, "Błąd: taki indeks już istnieje", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message, "Błąd: edycja danych", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }           
        }

        /// <summary>
        /// Displays confirmation mbox and removes student
        /// </summary>
        /// <param name="student">Student to be removed</param>
        public void TryRemove(Student student)
        {
            if (DialogResult.Yes == MessageBox.Show("Czy na pewno chcesz usunąć ucznia: " + student.FirstName + " " + student.Surname + "?",
                                                    "Usuń ucznia", MessageBoxButtons.YesNo, MessageBoxIcon.Question))
            {
                DataRow studentRow = dataset.Tables["uczniowie"].Rows[dataset.Tables["uczniowie"].Rows.IndexOf(dataset.Tables["uczniowie"].Rows.Find(student.Index))];
                List<Deposit> res = new List<Deposit>();

                foreach (DataRow dr in studentRow.GetChildRows("studentDepositRelation"))
                {
                    dr.Delete();
                }
                dataset.Tables["uczniowie"].Rows.Remove(dataset.Tables["uczniowie"].Rows.Find(student.Index));

                TrySave();
            }
        }

        public void TryRemoveLastDeposit(Student student)
        {
            DataRow studentRow = dataset.Tables["uczniowie"].Rows[dataset.Tables["uczniowie"].Rows.IndexOf(dataset.Tables["uczniowie"].Rows.Find(student.Index))];
            DataRow[] studentDeposits = studentRow.GetChildRows("studentDepositRelation");

            if (studentDeposits.Length.Equals(0))
                MessageBox.Show("Brak wpłat","Błąd", MessageBoxButtons.OK, MessageBoxIcon.Error);
            else
                if (DialogResult.Yes == MessageBox.Show("Czy na pewno chcesz usunąć ostatnią wpłatę?",
                                                        "Usuń wpłatę", MessageBoxButtons.YesNo, MessageBoxIcon.Question))
                {
                    dataset.Tables["wplaty"].Rows.Remove(studentDeposits[studentDeposits.Length - 1]);
                    TrySave();
                }
        }

        /// <summary>
        /// Displays MessageBox asking if user wants to save the database. If so, saves it, otherwise - no.
        /// </summary>
        public void TrySave()
        {
            try //backup copy
            {
                System.IO.FileStream file = new System.IO.FileStream("baza.ratuj", System.IO.FileMode.Create);
                System.IO.Compression.GZipStream backup = new System.IO.Compression.GZipStream(file, System.IO.Compression.CompressionMode.Compress);
                dataset.WriteXml(backup);
                file.Close();
            }
            catch(Exception)
            {
            }

            if (DialogResult.Yes == MessageBox.Show("Czy chcesz zapisać zmiany?", "Pytanie", MessageBoxButtons.YesNo, MessageBoxIcon.Question))
            {
                try
                {
                    Dataset.AcceptChanges();
                    Dataset.WriteXml("baza.xml");
                    System.IO.File.Delete("baza.ratuj");
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message, "Błąd: zapis bazy danych", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        /// <summary>
        /// Returns Student object from the database
        /// </summary>
        /// <param name="i">index in the database</param>
        /// <returns>Student object at the given position</returns>
        public Student GetStudent(int i)
        {
            return new Student(dataset.Tables["uczniowie"].Rows[i].ItemArray);
        }

        /// <summary>
        /// Returns Student object from the database
        /// </summary>
        /// <param name="index">index</param>
        /// <returns>Student object at the given position</returns>
        public Student GetStudent(String index)
        {
            return new Student(dataset.Tables["uczniowie"].Rows.Find(index).ItemArray);
        }

        private void TrySetBaseValue()
        {
            if (DialogResult.Yes == MessageBox.Show("Czy chcesz teraz wprowadzić kwotę bazową?", "Kwota bazowa", MessageBoxButtons.YesNo, MessageBoxIcon.Information))
            {
                frmBaseValue frmBase = new frmBaseValue();
                if (BaseValue.ToString().Length > 0)
                    frmBase.txtValue.Text = BaseValue.ToString();
                if (DialogResult.OK == frmBase.ShowDialog())
                {
                    SetBaseValue(uint.Parse(frmBase.txtValue.Text));
                }
            }
        }

        public void ExportPayslips(String path)
        {
            System.IO.StreamWriter writer = new System.IO.StreamWriter(path);
            try
            {
                for (int i = 0; i < dataset.Tables["uczniowie"].Rows.Count; i++)
                {
                    Student student = this.GetStudent(i);
                    if (student.hasRates)
                    {
                        writer.WriteLine(student.Index);
                        writer.WriteLine(student.FirstName);
                        writer.WriteLine(student.Surname);
                        writer.WriteLine(student.ZipCity.Split(' ')[student.ZipCity.Split(' ').Length-1]);
                        writer.WriteLine(student.GetRate((uint)DateTime.Now.Month-8)); //FIXME
                    }
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message, "Błąd: eksport pliku txt", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                writer.Close();
            }
        }

        public List<String[]> GetTodaysDeposits(String date)
        {
            List<String[]> res = new List<String[]>();
            int i = 1;
            int sum = 0;
            foreach (DataRow dr in this.dataset.Tables["wplaty"].Rows)
            {
                if (dr.ItemArray[1].Equals(date))
                {
                    String[] entry =new String[5];
                    entry[0] = i.ToString();
                    DataRow d = dataset.Tables["uczniowie"].Rows.Find(dr.ItemArray[0]);
                    entry[1] = d.ItemArray[1] + " " + d.ItemArray[2];
                    Array.Copy(dr.ItemArray, 2, entry, 2, dr.ItemArray.Length-2);
                    sum += int.Parse(entry[entry.Length - 1].ToString());
                    entry[entry.Length - 1] = int.Parse(entry[entry.Length - 1]).ToString("c0");
                    res.Add(entry);
                    ++i;
                }
            }
            res.Sort(SortByTitle);

            i = 1; //sorting L.p column
            foreach(String[] entry in res)
                entry[0] = i++.ToString();

            res.Add(new String[] {"","","","",sum.ToString("c0")});
        
            return res;
        }

        private static int SortByTitle(string[] x, string[] y)
        {
            return x[2].CompareTo(y[2]); ;
        }

        public List<String[,]> GetAllDeposits()
        {
            List<String[,]> res = new List<String[,]>();

            for (int i = 0; i < dataset.Tables["uczniowie"].Rows.Count; i++)
            {
                int sum = 0;
                DataRow studentRow = dataset.Tables["uczniowie"].Rows[i];
                String[,] entry = new String[2,13];
                Student st = new Student(studentRow.ItemArray);
                entry[0,0] = st.Surname;
                entry[0,1] = st.FirstName;
                entry[0,2] = st.Index;

                if (st.isLate)
                    entry[1,0] = "late";

                DataRow[] depositHistory = studentRow.GetChildRows("studentDepositRelation");
                int j = 0;
                for(j=0;j<depositHistory.Length;j++)
                {
                    String deposit = depositHistory[j]["Kwota"].ToString();
                    entry[0,j + 3] = int.Parse(deposit).ToString("c0");
                    sum += int.Parse(deposit);
                }

                uint price = 0;
                j = 1;
                for (j = 1; j < entry.GetLength(1) - 2; j++)
                {
                    price = st.GetRate((uint)(j));
                    entry[1, j + 2] = price.ToString("c0"); ;
                }
                
                entry[0,entry.GetLength(1) - 1] = sum.ToString("c0");
                entry[1, entry.GetLength(1) - 1] = st.GetTotalCost().ToString("c0");
                
                res.Add(entry);
            }

            res.Sort(SortByIndex);

            return res;
        }

        private static int SortByIndex(string[,] x, string[,] y)
        {
            return x[0, 2].CompareTo(y[0, 2]); ;
        }

        public bool isUnique(String index)
        {
            return !dataset.Tables["uczniowie"].Rows.Contains(index);
        }
    }
}
